﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class AddressDAL : IAddressDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public AddressDAL()
        { 
            
        }

        public DataSet LoadEmployeeAddress(int EmpSequence, Nullable<int> AddressType)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(1, "@AddressType", AddressType);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT * FROM("
                        + " SELECT a.ADDRESS_SEQ, a.ADDRESS_TYPE_SEQ, a.ADDRESS_DESCR, a.POSTAL_CODE"
                        + ", a.TEL, a.FAX, a.MOBILE, a.EMAIL"
                        + ", a.DISTRICT_SEQ, coalesce(a.PROVINCE_SEQ, pv.PROV_SEQ) as PROVINCE_SEQ, a.COUNTRY_SEQ"
                        + ", CONCAT(ISNULL(a.ADDRESS_DESCR, ''), ' ', ISNULL(d.DISTRICT_NAME, '')"
                            + ", ' ', ISNULL(ap.AMP_NAME, ''), ' ', ISNULL(pv.PROV_NAME, '')) AS ADDRESS_NAME"
                        + ", CONCAT('ตำบล', d.DISTRICT_NAME, ' อำเภอ', ap.AMP_NAME, ' จังหวัด', pv.PROV_NAME)"
							+ " AS DIS_AMP_PROV"
						+ ", d.DISTRICT_NAME, ap.AMP_NAME, pv.PROV_NAME "
                    + " FROM HRM_PS_EMP_ADDRESS a"
                    + " LEFT JOIN MAS_DISTRICTS d ON a.DISTRICT_SEQ = d.DISTRICT_SEQ"
                    + " LEFT JOIN MAS_AMPHURS ap ON d.AMP_SEQ = ap.AMP_SEQ"
                    + " LEFT JOIN MAS_PROVINCES pv ON ap.PROV_SEQ = pv.PROV_SEQ"
                    + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                         + " AND a.EMP_SEQ = @EmpSequence"
                         + " AND a.ADDRESS_TYPE_SEQ = '1'"
                    + " UNION ALL "
                    + " SELECT a.ADDRESS_SEQ, a.ADDRESS_TYPE_SEQ, a.ADDRESS_DESCR, a.POSTAL_CODE, a.TEL, a.FAX, a.MOBILE, a.EMAIL"
                        + ", a.DISTRICT_SEQ, coalesce(a.PROVINCE_SEQ, pv.PROV_SEQ) as PROVINCE_SEQ, a.COUNTRY_SEQ"
                        + ", CONCAT(ISNULL(a.ADDRESS_DESCR, ''), ' ', ISNULL(d.DISTRICT_NAME, '')"
                            + ", ' ', ISNULL(ap.AMP_NAME, ''), ' ', ISNULL(pv.PROV_NAME, '')) AS ADDRESS_NAME"
                        + ", CONCAT('ตำบล', d.DISTRICT_NAME, ' อำเภอ', ap.AMP_NAME, ' จังหวัด', pv.PROV_NAME)"
							+ " AS DIS_AMP_PROV"
						+ ", d.DISTRICT_NAME, ap.AMP_NAME, pv.PROV_NAME "
                    + " FROM HRM_PS_EMP_ADDRESS a"
                    + " LEFT JOIN MAS_DISTRICTS d ON a.DISTRICT_SEQ = d.DISTRICT_SEQ"
                    + " LEFT JOIN MAS_AMPHURS ap ON d.AMP_SEQ = ap.AMP_SEQ"
                    + " LEFT JOIN MAS_PROVINCES pv ON ap.PROV_SEQ = pv.PROV_SEQ"
                    + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                         + " AND a.EMP_SEQ = @EmpSequence"
                         + " AND a.ADDRESS_TYPE_SEQ = '2'"
                    + ")t ORDER BY t.ADDRESS_TYPE_SEQ, t.ADDRESS_SEQ";

                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public void InsertEmployeeAddress(AddressInfo info)
        {
            string strSQL;
            List<AddressInfo> infoList = new List<AddressInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@EmployeeSequence", info.Employee.Sequence);
                dbManager.AddParameters(1, "@AddressType", info.AddressType);
                dbManager.AddParameters(2, "@Address", info.Address);
                dbManager.AddParameters(3, "@PostalCode", info.PostalCode);
                dbManager.AddParameters(4, "@Phone", info.Phone);
                dbManager.AddParameters(5, "@Fax", info.Fax);
                dbManager.AddParameters(6, "@Mobile", info.Mobile);
                dbManager.AddParameters(7, "@Email", info.Email);
                dbManager.AddParameters(8, "@District", info.District == null ? (int?)null : info.District.Sequence);
                dbManager.AddParameters(9, "@Province", info.Province == null ? (int?)null : info.Province.Sequence);
                dbManager.AddParameters(10, "@Country", info.Country == null ? (int?)null : info.Country.Sequence);
                dbManager.AddParameters(11, "@City", info.City.Sequence == 0 ? (int?)null : info.City.Sequence);                
                dbManager.AddParameters(12, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@CreatedBy", info.CreatedBy);
                dbManager.AddParameters(14, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO HRM_PS_EMP_ADDRESS"
                        + "(EMP_SEQ, ADDRESS_TYPE_SEQ, ADDRESS_DESCR, POSTAL_CODE, TEL, FAX, MOBILE, EMAIL"
                        + ", DISTRICT_SEQ, PROVINCE_SEQ, COUNTRY_SEQ, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@EmployeeSequence, @AddressType, @Address, @PostalCode, @Phone, @Fax, @Mobile, @Email"
                        + ", @District, @Province, @Country, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }
        public void UpdateEmployeeAddress(AddressInfo info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@Sequence", info.Sequence);
                dbManager.AddParameters(1, "@AddressType", info.AddressType);
                dbManager.AddParameters(2, "@Address", info.Address);
                dbManager.AddParameters(3, "@PostalCode", info.PostalCode);
                dbManager.AddParameters(4, "@Phone", info.Phone);
                dbManager.AddParameters(5, "@Fax", info.Fax);
                dbManager.AddParameters(6, "@Mobile", info.Mobile);
                dbManager.AddParameters(7, "@Email", info.Email);
                dbManager.AddParameters(8, "@District", info.District == null ? (int?)null : info.District.Sequence);
                dbManager.AddParameters(9, "@Province", info.Province == null ? (int?)null : info.Province.Sequence);
                dbManager.AddParameters(10, "@Country", info.Country == null ? (int?)null : info.Country.Sequence);
                dbManager.AddParameters(11, "@City", info.City.Sequence == 0 ? (int?)null : info.City.Sequence);
                dbManager.AddParameters(12, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@UpdatedBy", info.UpdatedBy);
                dbManager.AddParameters(14, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE HRM_PS_EMP_ADDRESS"
                        + " SET ADDRESS_TYPE_SEQ = @AddressType"
                        + ", ADDRESS_DESCR = @Address"
                        + ", POSTAL_CODE = @PostalCode"
                        + ", TEL = @Phone"
                        + ", FAX = @Fax"
                        + ", MOBILE = @Mobile"
                        + ", EMAIL = @Email"
                        + ", DISTRICT_SEQ = @District"
                        + ", PROVINCE_SEQ = @Province"
                        + ", COUNTRY_SEQ = @Country"
                    //+ ", RECORD_STATUS = "
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE ADDRESS_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }
    }
}
